논리 삭제 컬럼을 인덱스에 포함할지 판단하기

논리 삭제 컬럼을 인덱스에 포함할지 판단하기

한눈에 보기

is_deleted는 값이 두 개뿐이라 단독 인덱스의 선택도가 낮지만, (owner_id, is_deleted, updated_at)처럼 앞 열로 범위를 줄인 뒤 활성 행의 정렬 구간을 만드는 데는 의미가 있다. 반대로 삭제 데이터가 거의 없고 owner 전체 조회도 중요하다면 중간의 삭제 열이 정렬 활용을 방해하고 쓰기 비용만 늘릴 수 있다. 실제 쿼리 조합과 데이터 생명주기로 결정해야 한다.

목차

문제가 되는 상황

노트 서비스가 실제 DELETE 대신 삭제 시각을 기록한다고 하자.

CREATE TABLE notes (
  id BIGINT PRIMARY KEY,
  owner_id BIGINT NOT NULL,
  title VARCHAR(200) NOT NULL,
  body TEXT NOT NULL,
  updated_at DATETIME(6) NOT NULL,
  deleted_at DATETIME(6) NULL
);

활성 노트 목록은 항상 삭제되지 않은 행만 조회한다.

SELECT id, title, updated_at
FROM notes
WHERE owner_id = :owner_id
  AND deleted_at IS NULL
ORDER BY updated_at DESC
LIMIT 20;

WHERE에 deleted_at IS NULL이 매번 등장하니 인덱스에도 넣고 싶어진다.

CREATE INDEX ix_notes_owner_deleted_updated
ON notes(owner_id, deleted_at, updated_at DESC);

이 인덱스가 좋은지는 “deleted_at이 조건에 있다”만으로 판단할 수 없다. 활성 행의 비율, 휴지통과 복구 기능, owner별 행 수, 삭제 후 실제 보관 기간, 다른 목록이 deleted_at 조건을 생략하는지를 함께 봐야 한다.

논리 삭제는 모든 조회에 조건을 추가한다

논리 삭제는 복구와 감사에는 편리하지만 데이터베이스에서 행이 사라지지 않는다. 애플리케이션의 거의 모든 일반 조회가 활성 조건을 포함해야 한다.

WHERE deleted_at IS NULL

조건을 빠뜨리면 삭제한 노트가 검색, 통계, 권한 검사에 다시 나타날 수 있다.

stateDiagram-v2
    [*] --> ACTIVE: 생성
    ACTIVE --> DELETED: deleted_at 기록
    DELETED --> ACTIVE: 복구
    DELETED --> PURGED: 보존 기간 후 물리 삭제
    PURGED --> [*]

논리 삭제는 단순 boolean 필터가 아니라 데이터 상태와 수명주기를 만든다. 인덱스도 이 주요 상태 전이와 조회를 지원해야 한다.

데이터가 남아 있다는 것은 책임도 남아 있다는 뜻이다

개인정보 삭제 요구, 보존 기간, 백업과 분석계 반영, 자식 데이터 처리 정책을 함께 정해야 한다. 무기한 논리 삭제는 테이블과 인덱스를 계속 키운다.

boolean 단일 인덱스가 자주 불리한 이유

is_deleted를 별도 boolean으로 두었다고 하자.

CREATE INDEX ix_notes_is_deleted
ON notes(is_deleted);

전체 1,000만 행 중 990만 행이 활성이라면 is_deleted = false는 테이블의 99%를 반환한다.

false: 9,900,000 rows
true:    100,000 rows

단일 보조 인덱스로 990만 기본 키를 찾고 본 테이블로 이동하는 것보다 전체 스캔이 나을 수 있다. 값 종류가 두 개라는 낮은 cardinality와 활성 값의 높은 반환 비율이 겹친다.

반대로 삭제 행이 1%라면 is_deleted = true를 찾는 정리 배치에는 단일 인덱스가 유용할 수 있다. 같은 열도 조건값에 따라 반환 비율이 다르다. 자세한 원리는 인덱스 선택도가 성능에 미치는 영향과 연결된다.

복합 인덱스 중간에서는 역할이 달라진다

다음 활성 목록 쿼리를 다시 보자.

SELECT id, title, updated_at
FROM notes
WHERE owner_id = 8421
  AND is_deleted = false
ORDER BY updated_at DESC
LIMIT 20;

CREATE INDEX ix_notes_owner_deleted_updated
ON notes(owner_id, is_deleted, updated_at DESC);

첫 열 owner_id가 전체 테이블을 한 사용자의 노트 구간으로 줄인다. is_deleted가 그 안에서 활성과 삭제 상태를 나누고, 활성 구간 안에서는 updated_at 순서로 정렬된다.

owner=8421, deleted=false, updated=09-02
owner=8421, deleted=false, updated=09-01
owner=8421, deleted=false, updated=08-30
...
owner=8421, deleted=true,  updated=08-29

DB는 활성 구간의 시작에서 20개를 읽고 멈출 수 있다. is_deleted가 99% false라 많이 줄이지 못하더라도 뒤의 updated_at을 활성 행 안에서 연속된 정렬로 만드는 역할을 한다.

하지만 다음 쿼리에는 같은 인덱스가 완벽하지 않다.

SELECT id, title, updated_at
FROM notes
WHERE owner_id = 8421
ORDER BY updated_at DESC
LIMIT 20;

owner 구간 안에서 false와 true가 먼저 나뉘므로 전체 상태를 합친 updated_at 순서가 아니다. 관리자 감사 화면처럼 활성·삭제 전체의 최신 변경을 봐야 한다면 (owner_id, updated_at)이 별도로 필요할 수 있다.

낮은 선택도 열도 뒤의 정렬을 어떤 그룹 안에 둘지 결정한다

복합 인덱스에서는 열 하나의 전역 선택도만 보지 말고 앞 접두로 줄인 범위와 ORDER BY까지 함께 본다.

삭제 데이터 비율이 판단을 바꾼다

두 서비스의 분포를 비교해 보자.

항목 서비스 A 서비스 B
owner당 평균 노트 50 50,000
삭제 비율 0.2% 60%
삭제 보관 기간 7일 무기한
활성 목록 호출 높음 높음

서비스 A는 owner 범위 자체가 작고 삭제 행도 거의 없다. (owner_id, updated_at)로 최신순을 읽으며 삭제된 몇 행을 필터링해도 LIMIT 20을 빨리 채울 수 있다. is_deleted를 중간에 넣는 효과가 작고 인덱스 폭과 상태 변경 비용만 늘 수 있다.

서비스 B는 같은 owner에 삭제 행이 많다. (owner_id, updated_at)로 최신순을 읽을 때 최근 삭제가 몰려 있으면 활성 20건을 찾기 위해 수천 행을 건너뛸 수 있다. (owner_id, is_deleted, updated_at)의 활성 구간이 훨씬 유리할 수 있다.

서비스 A
20건 반환을 위해 21건 읽음

서비스 B
20건 반환을 위해 3,500건 읽음

분포는 시간에 따라 변한다. 출시 직후에는 삭제 행이 거의 없지만 수년간 보관하면 비율과 인덱스 크기가 달라진다. 현재 수치뿐 아니라 보존 정책에 따른 1년 후 크기도 추정한다.

is_deleted와 deleted_at 중 무엇을 인덱싱할까

두 모델에는 장단점이 있다.

boolean과 시각을 둘 다 저장

is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
deleted_at DATETIME(6) NULL

활성 조건은 단순하지만 두 열이 불일치할 수 있다.

is_deleted=false, deleted_at='2026-09-01'  ← 모순

CHECK로 관계를 강제할 수 있다.

CONSTRAINT ck_notes_deleted_state
CHECK (
  (is_deleted = false AND deleted_at IS NULL)
  OR
  (is_deleted = true AND deleted_at IS NOT NULL)
)

deleted_at 하나로 상태 표현

deleted_at DATETIME(6) NULL

WHERE deleted_at IS NULL

중복 상태가 없고 삭제 시각도 보존한다. 다만 nullable 열의 정렬과 통계, 유니크 규칙에서 NULL 의미를 이해해야 한다.

휴지통 만료 작업은 삭제 시각 범위를 사용한다.

SELECT id
FROM notes
WHERE deleted_at < CURRENT_TIMESTAMP - INTERVAL 30 DAY
ORDER BY deleted_at
LIMIT 1000;

이 작업에는 (deleted_at, id)처럼 삭제 시각으로 시작하는 별도 인덱스가 더 직접적이다. 활성 목록용 (owner_id, deleted_at, updated_at)의 접두는 전역 만료 배치에 맞지 않는다.

활성 목록과 휴지통 목록은 서로 다른 쿼리다

일반 목록:

WHERE owner_id = :owner_id
  AND deleted_at IS NULL
ORDER BY updated_at DESC
LIMIT 20

휴지통:

WHERE owner_id = :owner_id
  AND deleted_at IS NOT NULL
ORDER BY deleted_at DESC
LIMIT 20

활성 목록은 updated_at 정렬이 중요하고, 휴지통은 삭제 시각 정렬이 자연스럽다. 하나의 인덱스가 두 정렬을 모두 완벽하게 지원하지 못할 수 있다.

INDEX (owner_id, deleted_at, updated_at)

deleted_at이 특정 값으로 고정되지 않는 IS NOT NULL 범위에서는 그 뒤 updated_at 정렬 활용이 제한된다. 휴지통 사용량이 낮다면 삭제 행을 좁힌 뒤 작은 결과를 정렬해도 충분할 수 있다. 사용 빈도가 높고 owner당 삭제 행이 많다면 별도 (owner_id, deleted_at DESC) 인덱스를 검토한다.

기능 핵심 조건 핵심 정렬 우선순위
활성 노트 owner, deleted_at IS NULL updated_at 매우 높음
휴지통 owner, deleted_at IS NOT NULL deleted_at 중간 또는 낮음
만료 purge deleted_at < cutoff deleted_at, id 배치 중요
관리자 전체 이력 owner updated_at 드문 관리 기능

인덱스는 기능 중요도와 호출량을 기준으로 최소 집합을 선택한다.

부분 인덱스를 지원하는 DB의 선택지

PostgreSQL은 조건을 만족하는 행만 포함하는 partial index를 지원한다. 활성 행만 인덱싱할 수 있다.

CREATE INDEX ix_active_notes_owner_updated
ON notes(owner_id, updated_at DESC)
WHERE deleted_at IS NULL;

인덱스에는 활성 행만 있으므로 크기가 작고, 키 중간에 deleted_at을 넣지 않아도 활성 목록의 owner·updated_at 순서를 제공한다.

SELECT id, title, updated_at
FROM notes
WHERE owner_id = :owner_id
  AND deleted_at IS NULL
ORDER BY updated_at DESC
LIMIT 20;

쿼리 조건이 인덱스 predicate를 논리적으로 만족한다고 옵티마이저가 판단할 수 있어야 한다. 지나치게 동적인 predicate나 쿼리 표현 차이는 사용을 방해할 수 있으므로 PostgreSQL Partial Index 공식 문서를 대상 버전 기준으로 확인한다.

부분 인덱스는 삭제 행 조회를 지원하지 않는다

휴지통과 purge에는 다른 접근 경로가 필요하다. 활성 데이터가 대부분이면 크기 절감 효과도 작을 수 있다.

MySQL에서 검토할 수 있는 대안

MySQL은 PostgreSQL과 같은 임의 predicate의 partial index를 동일한 형태로 제공하지 않는다. 기본 선택은 복합 인덱스다.

CREATE INDEX ix_notes_owner_deleted_updated
ON notes(owner_id, is_deleted, updated_at DESC);

특정 조건을 generated column이나 functional key로 표현하는 설계도 가능하지만 단순한 활성 목록에 무조건 더 낫지는 않다. 예를 들어 활성 행에만 owner 값을 반환하는 생성 열을 생각할 수 있다.

ALTER TABLE notes
ADD COLUMN active_owner_id BIGINT
GENERATED ALWAYS AS (
  CASE WHEN deleted_at IS NULL THEN owner_id ELSE NULL END
) VIRTUAL;

CREATE INDEX ix_notes_active_owner_updated
ON notes(active_owner_id, updated_at DESC);

조회는 생성 열과 동일한 의미를 사용한다.

WHERE active_owner_id = :owner_id
ORDER BY updated_at DESC

하지만 이 방식에는 다음 비용이 있다.

MySQL 8.4의 functional key는 hidden virtual generated column으로 구현되며 제약이 있다. MySQL CREATE INDEX 공식 문서를 기준으로 사용 가능 표현식과 버전을 확인한다. 대부분의 경우 먼저 단순 복합 인덱스와 실제 비용을 측정하는 편이 유지보수에 유리하다.

논리 삭제가 유니크 규칙에 미치는 영향

노트 제목이 owner 안에서 유일해야 한다고 하자.

UNIQUE (owner_id, title)

이 제약은 삭제된 노트도 제목을 계속 점유한다. 삭제 후 같은 제목을 재사용할 수 있는지 제품 정책을 정해야 한다.

정책 A: 삭제 후에도 제목 점유 → 기존 unique 유지
정책 B: 활성 노트끼리만 유일 → 조건부 유일성 필요
정책 C: 같은 제목 생성 시 삭제 노트 복구 → 서비스 로직

단순히 (owner_id, title, is_deleted)로 바꾸면 삭제된 같은 제목 두 개를 허용하지 않는 새 제약이 생긴다. 여러 번 생성·삭제할 수 있는 요구에는 맞지 않을 수 있다.

UNIQUE (owner_id, title, is_deleted)

활성 행에만 적용되는 partial unique index를 지원하는 DB라면 직접 표현할 수 있다.

CREATE UNIQUE INDEX uq_active_notes_owner_title
ON notes(owner_id, title)
WHERE deleted_at IS NULL;

DB별 대안은 다르다. 삭제 시 제목을 익명화하거나 세대 식별자를 넣는 방법도 있지만 감사·복구 요구와 충돌할 수 있다. 자세한 제약 설계는 Unique Constraint를 비즈니스 규칙으로 활용하기와 연결된다.

쓰기 비용과 장기적인 테이블 팽창

노트를 삭제하면 인덱스에서 단순히 사라지는 것이 아니라 활성 구간에서 삭제 구간으로 키가 이동한다.

Before
(owner=8421, deleted=false, updated=09-02)

After
(owner=8421, deleted=true, updated=09-02)

복구하면 다시 반대로 이동한다. is_deleted 또는 deleted_at이 여러 복합 인덱스에 반복되면 상태 변경 한 번이 모두를 갱신한다.

INDEX (owner_id, is_deleted, updated_at)
INDEX (workspace_id, is_deleted, updated_at)
INDEX (category_id, is_deleted, title)

논리 삭제 행이 무기한 쌓이면 테이블과 모든 보조 인덱스, 백업, 복제 비용이 계속 증가한다. “복구 가능”과 “영구 보관”은 같은 요구가 아니다. 30일 복구만 필요하다면 만료 배치로 물리 삭제하거나 아카이브 저장소로 옮길 수 있다.

ACTIVE → soft deleted, 30일 복구 가능
30일 경과 → archive 또는 physical purge

purge 배치는 한 번에 대량 삭제해 긴 잠금과 로그 폭증을 만들지 않도록 작은 batch와 진행 커서를 사용한다.

DELETE FROM notes
WHERE deleted_at < :cutoff
ORDER BY deleted_at, id
LIMIT 1000;

DB별 DELETE 문법과 트랜잭션 영향을 확인하고 batch 사이의 부하를 제어한다.

쿼리 누락을 방지하는 애플리케이션 구조

인덱스가 있어도 개발자가 deleted_at IS NULL 조건을 빠뜨리면 삭제 행이 반환된다. repository 메서드의 기본 범위를 명확히 한다.

class NoteRepository {
  findActiveById(ownerId: number, noteId: number) {
    return this.db.note.findFirst({
      where: {
        id: noteId,
        ownerId,
        deletedAt: null,
      },
    });
  }

  findDeletedForTrash(ownerId: number, cursor?: TrashCursor) {
    // 삭제 행 조회는 이름부터 명시적인 별도 경로로 둔다.
  }
}

ORM의 global scope나 middleware로 자동 필터를 적용할 수 있지만 예외 경로를 숨길 수 있다. 관리자·복구·relation preload에서 scope가 일관되게 적용되는지 테스트해야 한다.

다음 케이스를 통합 테스트로 고정한다.

it("삭제한 노트는 일반 목록과 단건 조회에서 보이지 않는다", async () => {
  const note = await fixtures.createNote({ ownerId: 42 });
  await noteService.softDelete({ ownerId: 42, noteId: note.id });

  await expect(noteService.findActive(note.id, 42)).resolves.toBeNull();
  await expect(noteService.listActive(42)).resolves.not.toContainEqual(
    expect.objectContaining({ id: note.id }),
  );
});

캐시 키와 검색 색인에서도 삭제 상태가 반영되어야 한다. DB에서만 숨기고 Elasticsearch 결과와 CDN 캐시에 남아 있으면 사용자에게 계속 보인다.

실행 계획과 테스트 시나리오

두 인덱스 후보를 실제 분포에서 비교한다.

-- 후보 A
CREATE INDEX ix_notes_owner_updated
ON notes(owner_id, updated_at DESC);

-- 후보 B
CREATE INDEX ix_notes_owner_deleted_updated
ON notes(owner_id, is_deleted, updated_at DESC);

EXPLAIN ANALYZE
SELECT id, title, updated_at
FROM notes
WHERE owner_id = 8421
  AND is_deleted = false
ORDER BY updated_at DESC
LIMIT 20;

테스트 분포를 여러 개 만든다.

시나리오 owner 전체 삭제 비율 최근 삭제 쏠림
일반 사용자 50 2% 없음
대형 사용자 500,000 2% 없음
장기 보관 사용자 500,000 60% 높음
휴지통 heavy 100,000 80% 매우 높음

확인할 항목은 다음과 같다.

인덱스 후보가 비슷하다면 더 단순하고 여러 핵심 쿼리를 지원하는 쪽을 우선한다. 중복 인덱스를 동시에 유지하는 비용은 인덱스가 많아질수록 쓰기가 느려지는 이유를 기준으로 판단한다.

결론

논리 삭제 열을 인덱스에 넣을지는 boolean의 낮은 선택도 하나로 결정할 수 없다. (owner_id, is_deleted, updated_at)에서는 owner로 범위를 줄인 뒤 활성 행을 하나의 정렬 구간으로 만들어 top-N 목록을 빠르게 끝내는 역할을 할 수 있다. 반대로 삭제 행이 거의 없고 owner당 데이터가 작다면 (owner_id, updated_at)로 읽으며 몇 행만 필터링하는 편이 단순하고 저렴할 수 있다.

활성 목록, 휴지통, 만료 purge와 관리자 이력은 조건과 정렬이 서로 다른 쿼리다. 모든 경로를 인덱스 하나로 해결하려 하지 말고 중요도와 호출량을 기준으로 선택한다. PostgreSQL의 partial index와 MySQL의 복합·generated column 대안은 기능과 유지보수 비용이 다르므로 대상 DB에서 검증해야 한다. 마지막으로 논리 삭제는 인덱스 문제 이전에 복구 기간, 유니크 값 재사용, 개인정보와 물리 purge까지 포함한 데이터 수명주기 설계다.

관련 노트

참고 자료